#!/bin/sh
#抽取dwc_lc_fault_info故障数据到dwd_fault_info表中,单车单故障每天只计算一次，最早出现和最晚出现时间分别为开始，终止时间
CUR_DIR=$(cd "$(dirname "${BASH_SOURCE-$0}")"; pwd)
UP_DIR=$(dirname $(dirname $(dirname "$PWD")))
echo ${UP_DIR}
source ${UP_DIR}/system_env.sh


START_TIME=$(date +%s)
hive_db=${hive_db}

if [ -z "${hive_db}" ];then
  hive_db=dataplatform_huashen
else
  hive_db=${hive_db}
fi


if [ -z "$1" ];then
  part_time=$(date -d "1 days ago" +%Y%m%d)
else
  part_time=$1
fi

sql_suffix=$(cat <<EOF
 insert overwrite table ${hive_db}.dwd_fault_info_pdi partition (part_time)
 select
   tid,
   (case when begintype = '2' then  unix_timestamp(  concat(nvl(cast(part_time as string),''),nvl(' 00:00:00','')),'yyyyMMdd HH:mm:ss') else begintime end) as beginTime,
   (case when begintype = '1' then  unix_timestamp(  concat(nvl(cast(part_time as string),''),nvl(' 23:59:59','')),'yyyyMMdd HH:mm:ss')  else endTime end)  as endTime,
   beginlongitude as beginLongitude,
   beginlatitude as beginLatitude,
   endlongitude as endLongitude,
   endlatitude as endLatitude,
   (s.endtime - s.begintime) as  duration,
   s.faultaddr,
   s.spn,
   s.fmi,
   CONCAT_WS('_',faultaddr,spn,fmi),
   part_time
   from
   (SELECT
       tid ,
       gpstime,
       first_value(gpstime)   over (partition by tid,part_time,faultaddr,spn,fmi  order by gpstime )  begintime,
       first_value(gpstime)   over (partition by tid,part_time,faultaddr,spn,fmi  order by gpstime desc )  endtime,
       first_value(faultType) over (partition by tid,part_time,faultaddr,spn,fmi  order by gpstime )  begintype,
       first_value(faultType) over (partition by tid,part_time,faultaddr,spn,fmi  order by gpstime desc )  endtype,
       first_value(longitude) over (partition by tid,part_time,faultaddr,spn,fmi  order by gpstime )  beginlongitude,
       first_value(latitude)  over (partition by tid,part_time,faultaddr,spn,fmi  order by gpstime )  beginlatitude,
       first_value(longitude) over (partition by tid,part_time,faultaddr,spn,fmi  order by gpstime desc )  endlongitude,
       first_value(latitude)  over (partition by tid,part_time,faultaddr,spn,fmi  order by gpstime  desc)  endlatitude,
       row_number() over (partition by tid,part_time,faultaddr,spn,fmi  order by gpstime  desc) row_number,
       faultaddr,
       spn,
       fmi,
       part_time
       from ${hive_db}.dwc_lc_faultinfo_pdi where part_time = ${part_time}
   ) s
   where row_number =1;
EOF
)

CUR_DIR=$(cd "$(dirname "$0")"; pwd)
hive_config=$(cat  "${CUR_DIR}"/hive.config)
sql="${hive_config}${sql_suffix}"
echo "导入dwc_lc_faultinfo_pdf的SQL为：${sql}"

#执行语句·
hive --hiveconf "tez.queue.name=${queue_name}" -e "${sql}"
if [ $? -ne 0 ];then
  echo "导入dwd_fault_info失败，耗时$((`date +%s`-START_TIME))秒..."
else
  echo "导入dwd_fault_info成功，耗时$((`date +%s`-START_TIME))秒..."
fi